from tools.mysqlconntool import mysqlconn
def create_psychological_test_record(user_id,num):
    sql = """
        INSERT INTO user_psychological_test_record (user_id,num)
        VALUES (%s,%s)
        """
    val = (user_id,num)
    result = mysqlconn.sql_update(sql, val)

    print(result)
    return True


def check_psychological_test_record_exists(user_id, num):
    sql = """
        SELECT EXISTS(
            SELECT 1 FROM user_psychological_test_record 
            WHERE user_id = %s AND num = %s
        )
    """
    val = (user_id, num)

    # 假设 mysqlconn.sql_query 是用来进行 SELECT 查询的函数，并且返回查询结果。
    result = mysqlconn.sql_select(sql, val)

    # 根据查询结果返回 True 或 False
    # 假设 sql_query 返回的是一个包含结果集的列表，我们检查第一个元素是否是 1 (存在)
    if result and result[0][0] == 1:
        print("记录存在。")
        return False
    else:
        print("记录不存在。")
        return True
def add_psychological_score(user_id, num,scale_name, score,scale_answers,answers):
    if check_psychological_test_record_exists(user_id,num):
        create_psychological_test_record(user_id,num)
    # 根据量表名称动态构建 SQL 语句
    sql = f"""
            UPDATE user_psychological_test_record
            SET {scale_name} = %s, {scale_answers} = %s 
            WHERE user_id = %s AND num = %s
            """
    val = (score, answers,user_id,num)
    mysqlconn.sql_update(sql, val)
#
# def sql_update(self, sql, val):
#     try:
#         # 获取数据库连接
#         mydb = self.get_connection()
#         mycursor = mydb.cursor()
#
#         # 执行 SQL 语句
#         mycursor.execute(sql, val)
#
#         # 提交更改
#         mydb.commit()
#
#         # 关闭游标和连接
#         mycursor.close()
#         mydb.close()
#
#         return True
#     except Exception as e:
#         print(f"Error executing SQL update: {str(e)}")
#         return False

# def add_psychological_score(user_id, scale_name, score):
#     # 检查字段是否为空
#     check_sql = f"""
#                  SELECT {scale_name}
#                  FROM user_psychological_test_record
#                  WHERE user_id = %s
#                  """
#     check_val = (user_id,)
#     result = mysqlconn.sql_select(check_sql, check_val)
#     print(f'result:{result}')
#     if result and result[0][0] is None:
#         # 字段为空，执行更新操作
#         update_sql = f"""
#                      UPDATE user_psychological_test_record
#                      SET {scale_name} = %s
#                      WHERE user_id = %s
#                      """
#         update_val = (score, user_id)
#         mysqlconn.sql_update(update_sql, update_val)
#     else:
#         # 字段不为空，插入新记录
#         insert_sql = f"""
#                      INSERT INTO user_psychological_test_record (user_id, {scale_name})
#                      VALUES (%s, %s)
#                      """
#         insert_val = (user_id, score)
#         mysqlconn.sql_update(insert_sql, insert_val)